const mysql = require('mysql');


let pool=mysql.createPool({
  host:'localhost',
  user:'root',
  password:'123456',
  database:'supermarket_system',
  port:3306,
  connectionLimit:50,//允许连接数
  multipleStatements : true,  //是否允许执行多条sql语句
  timezone:"08:00" //大坑，必须加这一句，否则时间不对劲
})
let query = (sql,...params) => {
  return new Promise((resolve,reject)=>{
    pool.getConnection((err,conn) => {
      if(err){
        console.log(err)
        return;
      }
      conn.query(sql,params,(error,res) => {
        // console.log(res);
        conn.release();
        if(error){
          return reject(error)
        }
        resolve(res)
      })
    })
  })
}

let login = (username,password) => {
  let sql = `select * from user where username = '${username}' and password = ${password}`;
  return sql;
}
let register =  (username,password) => {
  let sql = `insert into user(username,password) values('${username}',${password})`;
  return sql;
}
let stafflistByPage = (currentPage,pageSize) => {
  let sql = `select * from staff limit ${currentPage},${pageSize}`; //分页
  return sql;
}
let stafflist = (currentPage,pageSize) => {
  let sql = `select * from staff`; //分页
  return sql;
}
let stafflistTotalNum = () => {
  let sql = 'select count(*) as total from staff'
  return sql;
}

let findStaffById = id => {
  let sql = `select * from staff where id = ${id}`
  return sql;
}

let addStaff = (staffForm) => {
  // 在insert 里面， id自增且不想填写（里面有自增）的话，需要填入一个null 否则语句会报错。
  let sql = `insert into staff(id,name,sex,birthday,address,phone,job,states,presence) values(null,'${staffForm.name}','${staffForm.sex}','${staffForm.birthday}','${staffForm.address}','${staffForm.phone}','${staffForm.job}','${staffForm.state}',${staffForm.presence})`;
  return sql;
}
let updateStaff = (id,staffForm) => {
  let sql = `update staff SET name = '${staffForm.name}',sex = '${staffForm.sex}',birthday = '${staffForm.birthday}',address = '${staffForm.address}',phone = '${staffForm.phone}',job = '${staffForm.job}',state = '${staffForm.state}',presence = ${staffForm.presence} where id = ${id}`;
  return sql;
}

let deleteStaffById = id => {
  let sql = `delete from staff where id = ${id}`;
  return sql;
}
let findStaffByName = name => {
  let sql = `select * from staff where name like '%${name}%'`;
  return sql;
}

let queryStaffPresenceInfoByMonthAndId = (MonthForm,id) => {
  let sql = `select * from staff_presence_message where staff_id = ${id} and  clock_time between '${MonthForm[0]}' and '${MonthForm[1]}' group by msg_id`;
  return sql;
}
let queryStaffPresenceInfoById = (id) => {
  let sql = `select * from staff_presence_message where staff_id = ${id}`;
  return sql;
}




let goodslistByPage = (currentPage,pageSize) => {
  let sql = `select * from goods,category where goods.cat_id = category.cat_id limit ${currentPage},${pageSize}`; //分页
  return sql;
}
let goodslist = () => {
  let sql = `select * from goods,category where goods.cat_id = category.cat_id`; //分页
  return sql;
}
let goodslistTotalNum = () => {
  let sql = 'select count(*) as total from goods'
  return sql;
}
let findGoodsById = id => {
  let sql = `select * from goods,category where goods.id = ${id} and goods.cat_id = category.cat_id`
  return sql;
}
let queryCategoryList = () =>  {
  let sql = 'select * from category';
  return sql;
}
let addGoodsInfoById = (msg,id) => {
  let sql = `insert into goods_message(id,msg,goods_id) values(null,'${msg}',${id}`;
  return sql;
}
let updateGoodsNumById =  (editNumForm) => {
  let sql = `update goods SET number =  ${parseInt(editNumForm.next_number)},update_time = '${editNumForm.update_time}' where id = ${editNumForm.id}`;
  return sql;
}
let addGoods = (goodsForm) => {
  let sql = `insert into goods(id,name,price,number,cat_id,add_time) values(null,'${goodsForm.name}','${parseFloat(goodsForm.price)}','${goodsForm.number}','${parseInt(goodsForm.cat_id)}','${goodsForm.add_time}')`;
  return sql;
}
// 我擦 低级错误，忘记改表名字了
let updateGoods = (editGoodsForm) => {
  let sql = `update goods SET name = '${editGoodsForm.name}',price = ${parseFloat(editGoodsForm.price)},cat_id = '${editGoodsForm.cat_id}',update_time = '${editGoodsForm.update_time}' where id = ${editGoodsForm.id}`;
  return sql;
}
let addGoodsMsgInfo = (updateNumForm) => {
  let sql = `insert into goods_message(msg_id,msg_user,prev_number,next_number,result_number,goods_id,goods_name,update_time) values(null,'${updateNumForm.msg_user}',
${parseInt(updateNumForm.prev_number)},${parseInt(updateNumForm.next_number)},${parseInt(updateNumForm.next_number) - parseInt(updateNumForm.prev_number)},${parseInt(updateNumForm.id)},
'${updateNumForm.name}','${updateNumForm.update_time}')`;
  return sql;
}
let goodsInfoListById = id => {
  let sql = `select * from goods_message where goods_id = ${id}`;
  return sql;
}

let queryGoodsInfoByMonthAndId = (MonthForm,id) => {
  let sql = `select * from goods_message where goods_id = ${id} and  update_time between '${MonthForm[0]}' and '${MonthForm[1]}' group by msg_id`;
  return sql;
}
let queryGoodsInfoByMonthAndIdCountResultNumber = (MonthForm,id) => {
  let sql = `select sum(result_number) as result from goods_message where goods_id = ${id} and update_time between '${MonthForm[0]}' and '${MonthForm[1]}'`;
  return sql;
}
let deleteGoodsById = id => {
  let sql = `delete from goods where id = ${id}`;
  return sql;
}


let querySellGoodsInfoByMonthAndId = (MonthForm,id) => {
  let sql = `select * from sell_goods_message where goods_id = ${id} and  sell_time between '${MonthForm[0]}' and '${MonthForm[1]}' group by msg_id`;
  return sql;
}
let querySellGoodsInfoById = (id) => {
  let sql = `select * from sell_goods_message where goods_id = ${id}`;
  return sql;
}

module.exports = {
  query,
  login,
  register,

  stafflistByPage,
  stafflist,
  stafflistTotalNum,
  addStaff,
  updateStaff,
  findStaffById,
  deleteStaffById,
  findStaffByName,
  queryStaffPresenceInfoByMonthAndId,
  queryStaffPresenceInfoById,



  goodslistByPage,
  goodslist,
  goodslistTotalNum,
  findGoodsById,
  queryCategoryList,
  addGoodsInfoById,
  updateGoodsNumById,
  addGoods,
  updateGoods,
  addGoodsMsgInfo,
  goodsInfoListById,
  queryGoodsInfoByMonthAndId,
  queryGoodsInfoByMonthAndIdCountResultNumber,
  deleteGoodsById,
  querySellGoodsInfoByMonthAndId,
  querySellGoodsInfoById

}


